﻿using MCInsertDb;
using MCInsertDb.DAL;
using SHANGBN1.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;

namespace SHANGBN1.DAL
{
    public class HPXXDAL
    {
        private HPXX ToCustomer(DataRow row)
        {
            HPXX hpxx = new HPXX();

            hpxx.Ckh = Convert.ToString(row[0]);
            hpxx.Hphm = Convert.ToString(row[1]);

            return hpxx;
        }
        private HPXX ToCustomer_GetAll(DataRow row)
        {
            HPXX hpxx = new HPXX();

            hpxx.Hphm = Convert.ToString(row["hphm"]);
            hpxx.Rksj = Convert.ToDateTime(row["rksj"]);
            hpxx.Ckh = Convert.ToString(row["ckh"]);
            hpxx.Cksj = Convert.ToDateTime(row["cksj"]);

            return hpxx;
        }

        public HPXX[] GetAll(DateTime zpsj)
        {
            HPXX[] zpclxxs = null;
            try
            {
                DataTable table = new DataTable();
                table = SqlHelper.ExecuteDataTable(@"select count(*) AA, zpr from zpclxx
	                        where zpsj like :zpsj group by zpr",
                            new OracleParameter(":zpsj", zpsj));
                zpclxxs = new HPXX[table.Rows.Count];
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow row = table.Rows[i];
                    zpclxxs[i] = ToCustomer(row);
                }
            }
            catch (System.Exception ex)
            {
                LogHelper.WriteLog(typeof(HPXXDAL), ex, ex.Message);

            }
            return zpclxxs;
        }

        public HPXX[] GetAll(DateTime zpsj, string scbj)
        {
            DataTable table = new DataTable();
            table = SqlHelper.ExecuteDataTable(@"select count(*) AA, zpr from zpclxx
                        where zpsj like :zpsj and scbj=:scbj group by zpr",
                        new OracleParameter(":zpsj", zpsj),
                        new OracleParameter(":scbj", scbj));
            HPXX[] zpclxxs = new HPXX[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                zpclxxs[i] = ToCustomer(row);
            }
            return zpclxxs;
        }
        public HPXX[] GetAll(string zpr, DateTime zpsj, string scbj)
        {
            DataTable table = new DataTable();
            table = SqlHelper.ExecuteDataTable(@"select * from zpclxx
                        where zpsj like :zpsj and scbj=:scbj and zpr=:zpr",
                        new OracleParameter(":zpsj", zpsj),
                        new OracleParameter(":scbj", scbj),
                        new OracleParameter(":zpr", zpr));
            HPXX[] zpclxxs = new HPXX[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                zpclxxs[i] = ToCustomer_GetAll(row);
            }
            return zpclxxs;
        }

        public DataRow GetById(string hphm)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(@"select * from HPXX 
                    where hphm=:hphm",
                new OracleParameter(":hphm", hphm));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0];
                //string value = row[0].ToString();
                return row;
            }
        }

        public int GetByHphm(string hphm)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(@"select * from HPXX 
                    where hphm=:hphm",
                new OracleParameter(":hphm", hphm));
            return dt.Rows.Count;
        }

        public int Insert(HPXX hpxx)
        {
            int returnValue = 0;
            //to_date(:JGSJ,'yyyy-mm-dd hh24:mi:ss')
            returnValue = SqlHelper.ExecuteNonQuery(@"INSERT INTO HPXX
                        (HPHM, RKSJ, CKH, CKSJ)
                 VALUES
                       (:HPHM, :RKSJ, :CKH, :CKSJ)",

                new OracleParameter(":HPHM", hpxx.Hphm),
                new OracleParameter(":RKSJ", hpxx.Rksj),
                new OracleParameter(":CKH", hpxx.Ckh),

                new OracleParameter(":CKSJ", hpxx.Cksj));
            return returnValue;

        }
        public int Update(HPXX hpxx)
        {
            int returnValue = 0;
            returnValue = SqlHelper.ExecuteNonQuery(@"UPDATE HPXX
                        SET CKSJ = :CKSJ,lqzt=1
                           where HPHM=:HPHM and CKH=:CKH",
                new OracleParameter(":CKSJ", hpxx.Cksj),
                new OracleParameter(":HPHM", hpxx.Hphm),
                new OracleParameter(":CKH", hpxx.Ckh));

            return returnValue;
        }

        public DataRow GetById_YELLOW(string hphm)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(@"select * from HPXX_YELLOW 
                    where hphm=:hphm",
                new OracleParameter(":hphm", hphm));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0];
                //string value = row[0].ToString();
                return row;
            }
        }

        public int GetByHphm_YELLOW(string hphm)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(@"select * from HPXX_YELLOW 
                    where hphm=:hphm",
                new OracleParameter(":hphm", hphm));
            return dt.Rows.Count;
        }

        public int Insert_YELLOW(HPXX hpxx)
        {
            int returnValue = 0;
            //to_date(:JGSJ,'yyyy-mm-dd hh24:mi:ss')
            returnValue = SqlHelper.ExecuteNonQuery(@"INSERT INTO HPXX_YELLOW
                        (HPHM, RKSJ, CKH, CKSJ)
                 VALUES
                       (:HPHM, :RKSJ, :CKH, :CKSJ)",

                new OracleParameter(":HPHM", hpxx.Hphm),
                new OracleParameter(":RKSJ", hpxx.Rksj),
                new OracleParameter(":CKH", hpxx.Ckh),

                new OracleParameter(":CKSJ", hpxx.Cksj));
            return returnValue;

        }
        public int Update_YELLOW(HPXX hpxx)
        {
            int returnValue = 0;
            returnValue = SqlHelper.ExecuteNonQuery(@"UPDATE HPXX_YELLOW
                        SET CKSJ = :CKSJ,lqzt=1
                           where HPHM=:HPHM and CKH=:CKH",
                new OracleParameter(":CKSJ", hpxx.Cksj),
                new OracleParameter(":HPHM", hpxx.Hphm),
                new OracleParameter(":CKH", hpxx.Ckh));

            return returnValue;
        }
    }
}
